import os,sys,sqlite3,copy

##################################################
class MLDictRecord(object):
  pass

class MLDictDataModel(object):
  def CreateDict(self,fileName,fieldNames):
    self.con=sqlite3.connect(fileName)
    self.con.isolation_level=None
    self.cur=self.con.cursor()
    self.tblName='dictionary'
    self.fieldTbl='metadict'
    self.cur.execute('create table %s(field_name)' % self.fieldTbl)
    self.cur.execute('create table %s(RID int NOT NULL)' % self.tblName)
    self.fields=['RID']
    self.maxRowId=1
    for ef in fieldNames:
      self.AddField(ef)

  def LoadDict(self,fileName):
    self.con=sqlite3.connect(fileName)
    self.con.isolation_level=None
    self.cur=self.con.cursor()
    self.tblName='dictionary'
    self.fieldTbl='metadict'
    self.fields=['RID']
    self.cur.execute('select * from %s' % self.fieldTbl)
    for ef in self.cur:
      self.fields.append(ef[0])
    self.cur.execute('select max(RID) from %s' % (self.tblName))
    self.maxRowId=self.cur.fetchone()[0]
    if self.maxRowId==None: self.maxRowId=1

  def CloseDict(self):
    self.con.commit()
    self.cur.close()

  def getFields(self):
    flds=copy.copy(self.fields)
    flds.remove('RID')
    return flds

  def getPersistedFields(self):
    return self.fields

  def getNextRowId(self):
    self.maxRowId+=1
    return self.maxRowId

  def AddField(self, fieldName):
    if fieldName not in self.fields:
      self.cur.execute('insert into %s values("%s")' % (self.fieldTbl,fieldName))
      self.cur.execute('alter table %s add %s text' % (self.tblName,fieldName))
      self.fields.append(fieldName)
      self.con.commit()

  def RemoveField(self, fieldName):
    if fieldName in self.fields:
      self.cur.execute('delete from %s where field_name="%s"' % (self.fieldTbl, fieldName))
      self.fields.remove(fieldName)
      validFields=""
      for ef in self.fields:
        validFields+=ef+","
      validFields=validFields.strip(',')
      self.cur.execute("begin transaction")
      self.cur.execute("create temporary table %s_backup (%s)" % (self.tblName,validFields))
      self.cur.execute("insert into %s_backup select %s from %s" % (self.tblName,validFields,self.tblName))
      self.cur.execute("drop table %s" % (self.tblName))
      self.cur.execute("create table %s(%s)" % (self.tblName,validFields))
      self.cur.execute("insert into %s select %s from %s_backup" % (self.tblName,validFields,self.tblName))
      self.cur.execute("drop table %s_backup" % self.tblName)
      self.cur.execute("commit")
      self.con.commit()

  def ModifyField(self,origName,newName):
    pass

  def CreateRecord(self):
    rec=MLDictRecord()
    for ef in self.fields:
      setattr(rec,ef,'')
    return rec

  def InsertValues(self,vals):
    for ev in vals:
      nbuf=''
      vbuf=''
      for em in dir(ev):
        val=str(getattr(ev,em))
        if not callable(em) and not em.startswith('__') and len(val)!=0 and val!=None:
          nbuf+='%s,' % (em)
          vbuf+='%s,' % (val)
      nbuf=nbuf.strip(',')
      vbuf=vbuf.strip(',')
      self.cur.execute('insert into %s(%s) values(%s)' % (self.tblName,nbuf,vbuf))
    self.con.commit()

  def RemoveValues(self,vals):
    for ev in vals:
      self.cur.execute('delete from %s where RID=%s' % (self.tblName,ev.RID))
    self.con.commit()

  def UpdateValues(self,vals):
    for ev in vals:
      buf=''
      RID=ev.RID
      if RID==None or len(RID.strip())==0: continue
      delattr(ev,'RID')
      for em in dir(ev):
        val=str(getattr(ev,em))
        if not callable(em) and not em.startswith('__') and val!=None and len(val)!=0:
          buf+='%s="%s",' % (em,val)
      buf=buf.strip(',')
      if len(buf)>0:
        self.cur.execute('update %s set %s where RID=%s' % (self.tblName,buf,RID))
    self.con.commit()

  def GetValues(self,fieldName=None):
    if fieldName:
      self.cur.execute('select * from %s order by %s' % (self.tblName,fieldName))
    else:
      self.cur.execute('select * from %s' % (self.tblName))
    vals=[]
    for ev in self.cur:
      tmp=MLDictRecord()
      idx=0
      for em in self.fields:
        setattr(tmp,em,ev[idx])
        idx+=1
      vals.append(tmp)
    self.con.commit()
    return vals

##################################################

